package com.tbsoft.ehr.dao.staffTransfer;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.StaffInfo;
import com.tbsoft.ehr.model.TransferInfo;

public class TransferDaoImpl extends BaseDaoImpl<TransferInfo> implements TransferDao {

	@Override
	public int getTransferCount(TransferInfo info,List<StaffInfo> staffLst, String transferDateFrom,String transferDateTo) {
		
		StringBuffer sb = new StringBuffer("select count(*) from TransferInfo ti where ti.status <> 'D' ");
		if(info.getSc()!=null){
			sb.append("and ti.sc = '").append(info.getSc().getId()).append("' ");
		}
		if(info.getTransferType()!=null&&!info.getTransferType().isEmpty()){
			sb.append("and ti.transferType = '").append(info.getTransferType()).append("' ");
		}
		if(transferDateFrom!=null&&transferDateTo!=null){
			sb.append("and ti.transferDate between '").append(transferDateFrom).append("' and '").append(transferDateTo).append("' ");
		}
		
		StringBuffer staffIdSb = new StringBuffer();
		if(staffLst!=null) {
			staffIdSb.append("and ti.staff in(");
			for(StaffInfo si:staffLst) {
				staffIdSb.append("'").append(si.getStaffId()).append("',");
			}
			int i = staffIdSb.lastIndexOf(",");
			staffIdSb.replace(i, i+1, ") ");
		} 
		final String queryStr = sb.toString()+staffIdSb.toString();
		
		Long count = (Long) getHibernateTemplate().execute(
				new HibernateCallback() {					
					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						return session.createQuery(queryStr).uniqueResult();
					}
				});
		return count.intValue();
	}

	@Override
	public TransferInfo getTransferById(final String id) {		
		return (TransferInfo)getHibernateTemplate().get(TransferInfo.class, id);
	}

	@Override
	public List<TransferInfo> getTransfersByStaffId(final String staffId) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("from TransferInfo ts where ts.staff = ? and ts.status <> 'D' order by ts.transferDate asc");
				q.setString(0, staffId);
				return q.list();
			}
		});
	}

	@Override
	public List<TransferInfo> getTransfersByCondton(TransferInfo info,
			List<StaffInfo> staffLst, String transferDateFrom,
			String transferDateTo, final int startIndex,final int count) {
		StringBuffer sb = new StringBuffer("from TransferInfo ti where ti.status <> 'D' ");
		if(info.getSc()!=null){
			sb.append("and ti.sc = '").append(info.getSc().getId()).append("' ");
		}
		if(info.getTransferType()!=null&&!info.getTransferType().isEmpty()){
			sb.append("and ti.transferType = '").append(info.getTransferType()).append("' ");
		}
		if(transferDateFrom!=null&&transferDateTo!=null){
			sb.append("and ti.transferDate between '").append(transferDateFrom).append("' and '").append(transferDateTo).append("' ");
		}
		
		StringBuffer staffIdSb = new StringBuffer();
		if(staffLst!=null){
			staffIdSb.append("and ti.staff in(");
			for(StaffInfo si:staffLst) {
				staffIdSb.append("'").append(si.getStaffId()).append("',");
			}
			staffIdSb.append("'')");
		} 
		final String queryStr = sb.toString()+staffIdSb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				return session.createQuery(queryStr).setFirstResult(startIndex).setMaxResults(count).list();
			}
		});
	}
	
}
